package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import Model.Person;
import Model.Phone;

public class DAO_H2 implements DAO 
{
	private Statement statement = null;
	private Connection connection = null;
	private String query="";
	private ResultSet result = null;
	
	
	public void Connect()
	{ 
		try
		{
			Class.forName("org.h2.Driver");
			connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public void Disconnect()
    {   
        try 
        {  
            connection.close();   
        } 
        catch (Exception e) 
        {   
            e.printStackTrace();   
        }  
    }
	
	@Override
	public void Create(ArrayList<Person> list) throws SQLException
	{
		statement = connection.createStatement(); 
		if (list!=null)
		{
			for (Person data : list)
			{
				query="INSERT INTO PERSONS VALUES ("+data.getId()+", '"+
						data.getFistname()+"', '"+data.getSurname()+
						"', "+data.getAge()+");";
				
				statement.execute(query);
				if (data.getPhone()!=null)
				{
					for (Phone phone : data.getPhone())
					{
						query="INSERT INTO PHONES VALUES ( NULL, '"+
								phone.getPhone()+"', "+data.getId()+");";
						statement.execute(query);
					} 
				}
			}
		}
		statement.close();
	}
	
	@Override
	public void Update(Person person) throws SQLException
	{
		statement = connection.createStatement();
		if (person!=null)
		{
			query="UPDATE PERSONS SET "+
					"FISTNAME='"+person.getFistname()+"', SURNAME='"+
					person.getSurname()+"', AGE="+person.getAge()+
					" WHERE ID="+person.getId()+";";
			statement.execute(query);
			
			query="DELETE FROM PHONES WHERE PERSONID="+person.getId()+";";
			statement.execute(query);
			
			for (Phone phone : person.getPhone())
			{
				query="INSERT INTO PHONES VALUES ( NULL, '"+
						phone.getPhone()+"', "+person.getId()+");";
				statement.execute(query);
			} 
		}
		statement.close();
	}

	@Override
	public void Delete(int id) throws SQLException 
	{
		statement = connection.createStatement();
		query="DELETE FROM PHONES WHERE PERSONID="+id+";";
		statement.execute(query);
		query="DELETE FROM PERSONS WHERE ID="+id+";";
		statement.execute(query);
		statement.close();
	}
	
	@Override
	public int MaxId() throws SQLException 
	{
		int id=1;
		
		statement = connection.createStatement();
		query="SELECT MAX(ID) FROM PERSONS;";
		statement.execute(query);
		
		result = statement.executeQuery(query);
		result.next();
		id = result.getInt("MAX(ID)");
		
		statement.close();
		return id;
	}

	@Override
	public void DeleteAll() throws SQLException 
	{
		statement = connection.createStatement();
		query="DELETE FROM PERSONS;";
		statement.execute(query);
		query="DELETE FROM PHONES;";
		statement.execute(query);
		statement.close();
	}

	@Override
	public Person Read(int id) throws SQLException 
	{
		statement = connection.createStatement();
		Person person=null;
		ArrayList<Phone> phones = null;
		
		query="SELECT * FROM PERSONS WHERE ID="+id+";";
		result = statement.executeQuery(query);
		result.next();
		
		if (result.getRow()==1)
		{
			person = new Person();   
			person.setId(result.getInt("ID"));
			person.setFistname(result.getNString("FISTNAME"));
			person.setSurname(result.getNString("SURNAME"));
			person.setAge(result.getInt("AGE"));
			
			Statement statementPhone = connection.createStatement();

			query="SELECT * FROM PHONES WHERE PERSONID="+id+";";
			ResultSet resultPhone = statementPhone.executeQuery(query);
			phones = new ArrayList<Phone>();
			
			while (resultPhone.next())
			{	
				Phone phone = new Phone();
				phone.setPhone(resultPhone.getNString("PHONE"));
				phones.add(phone);
			}
			statementPhone.close();
			person.setPhone(phones);
		}
		statement.close();
		return person;
	}

	@Override
	public ArrayList<Person> ReadAll() throws SQLException 
	{
		statement = connection.createStatement();
		ArrayList<Person> list = new ArrayList<Person>();
		ArrayList<Phone> phones;
		Person person;
		
		query="SELECT * FROM PERSONS;";
		result = statement.executeQuery(query);
	
		while ( result.next() ) 
		{   
			person = new Person();
			person.setId(result.getInt("ID"));
			person.setFistname(result.getNString("FISTNAME"));
			person.setSurname(result.getNString("SURNAME"));
			person.setAge(result.getInt("AGE"));
			
			phones = new ArrayList<Phone>();
			
			query="SELECT * FROM PHONES WHERE PERSONID="+person.getId()+";";
			Statement statementPhone = connection.createStatement();
			ResultSet resultPhone = statementPhone.executeQuery(query);
			
			while (resultPhone.next())
			{	
				Phone phone = new Phone();
				phone.setPhone(resultPhone.getNString("PHONE"));
				phones.add(phone);
			}
			resultPhone.close();
			person.setPhone(phones);
			list.add(person);
		}
		statement.close();
		return list;
	}
}
